package zucc.edu.cn.chengchengparttime;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import androidx.annotation.Nullable;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;

import zucc.edu.cn.chengchengparttime.LoginAndReg.LoginActivity;

public class dbHomeHelper extends SQLiteOpenHelper {
    private Context context;
    private SQLiteDatabase db;

    public dbHomeHelper(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version) {
        super(context,name,factory, version);
        this.context=context;
        db = getReadableDatabase();
    }

    //只执行一次
    @Override
    public void onCreate(SQLiteDatabase db) {
        //1.创建发布方表
        db.execSQL("DROP TABLE if exists tbl_publisher");
        db.execSQL("CREATE TABLE if not exists tbl_publisher(PublisherID TEXT PRIMARY KEY,Name TEXT,Position TEXT,Call TEXT,NumOfLikes INTEGER);");

        //2.创建兼职信息表
        db.execSQL("DROP TABLE if exists tbl_work");
        db.execSQL("CREATE TABLE if not exists tbl_work(WorkID INTEGER PRIMARY KEY AUTOINCREMENT,WorkSerials TEXT,WorkName TEXT," +
                "WorkContent TEXT,WorkTime TEXT,WorkArea TEXT,SalaryPerHour REAL,PaymentWay TEXT,PublisherInfo TEXT," +
                "PublisherName TEXT,NumofLikes INTEGER,constraint PublisherInfo foreign key(PublisherInfo) references tbl_publisher(Call) " +
                "on delete cascade on update cascade, constraint PublisherName foreign key(PublisherName) references tbl_publisher(Name) " +
                "on delete cascade on update cascade, constraint NumofLikes foreign key(NumofLikes) references tbl_publisher(NumOfLikes) " +
                "on delete cascade on update cascade);");

        //3.创建我的报名表
        db.execSQL("DROP TABLE if exists tbl_signup");
        db.execSQL("CREATE TABLE if not exists tbl_signup(SignupID INTEGER PRIMARY KEY AUTOINCREMENT,UserID TEXT,WorkSerials TEXT,WorkName TEXT,WorkContent TEXT,WorkTime TEXT," +
                "WorkArea TEXT,SalaryPerHour REAL,PaymentWay TEXT,PublisherInfo TEXT,SignupCreateTime TEXT,SignupStatus TEXT,IsLike TEXT," +
                "constraint PublisherInfo foreign key(PublisherInfo) references tbl_publisher(Call) );");

        //4.创建User
        db.execSQL("DROP TABLE if exists tbl_user");
        db.execSQL("CREATE TABLE if not exists tbl_user(UserID TEXT PRIMARY KEY,Password TEXT,Sno TEXT,Name TEXT,School TEXT,Gender TEXT," +
                "PhoneNub TEXT,IntensionType1 TEXT,IntensionType2 TEXT,RegisterTime TEXT);");


//        Log.i("Create some triggers", "开始创建触发器");
//        db.execSQL("CREATE TRIGGER fk_update_work AFTER UPDATE ON tbl_publisher FOR EACH ROW " +
////                "WHEN new.NumofLikes!=old.NumofLikes " +
//                "BEGIN " +
//                    "UPDATE tbl_work " +
//                    "SET NumofLikes=new.NumofLikes " +
////                    "WHERE old.PublisherInfo=old.NumofLikes; " +
//                    "WHERE PublisherInfo=(SELECT w.PublisherInfo from tbl_publisher p,tbl_work w where p.Name=w.PublisherName and p.Call=w.PublisherInfo and p.NumOfLikes=w.NumofLikes)" +
//                        " AND old.NumofLikes<>new.NumofLikes; " +
//                "END;"); //更新点赞数
//        Log.i("Create some triggers", "成功创建触发器");

        //添加发布方信息
        Log.d("add publisher info","开始添加发布方信息");
        String[] publisherid={"Pub1","Pub2","Pub3","Pub4","Pub5","Pub6","Pub7","Pub8","Pub9","Pub10","Pub11","Pub12","Pub13","Pub14","Pub15","Pub16","Pub17","Pub18","Pub19","Pub20"};
        String[] publishername={"陈阿姨","朱老师","宋老师","陈老师","李老师","马老师","计算学院学工办","李先生","张老师","林老师","龚老师","宋女士","朱老师","马先生","郑老师","马先生","陈老师","胡老师","李女士","郑老师"};
        String[] position={"档口负责人","教师","教师","教师","教师","教师","教师","档口负责人","教师","教师","教师","档口负责人","教师","档口负责人","教师","档口负责人","教师","教师","档口负责人","教师"};
        String[] call={"12345678910","16846556120","15466562321","18809216321","13315551333","16545313546","0571-88018801","0571-87677755","0571-87677753","0571-87677751","0571-87677750","0571-87677758","0571-87677759",
                "0571-87677725","0571-87687725","0571-87677225","0571-87617725","0571-87677620","0571-88077625","0571-88667603"};
        int[] numoflikes={45,46,50,28,65,45,75,80,40,41,35,46,67,38,65,90,91,79,56,30};
        for(int i=0;i<20;i++){
            ContentValues contentValues=new ContentValues();
            contentValues.put("PublisherID",publisherid[i]);
            contentValues.put("Name",publishername[i]);
            contentValues.put("Position",position[i]);
            contentValues.put("Call",call[i]);
            contentValues.put("NumOfLikes",numoflikes[i]);
            db.insert("tbl_publisher",null,contentValues);
        }
        Log.d("add publisher info","成功添加发布方信息");

        //添加兼职信息
        Log.d("add work info","开始添加兼职信息");
        String[] serials={"服务员","学生助理","学生助理","讲解员","助教","体测工作人员","军训助理","服务员","学生助理","学生助理","学生助理","服务员","学生助理","服务员","学生助理","服务员","学生助理","学生助理","服务员","学生助理"};
        String[] name={"养生猪肚鸡招学生兼职","学校图书馆招学生助理","人事处招学生助理","校史馆讲解员","数据结构基础课程助教","计算学院体测招工作人员","军训副连长","陈家河集装箱小屋销售员","图书馆信息参考中心助理","食堂文明用餐督导员","校友总会发展联络办公室工作助理","文六咖啡馆服务员","妇联媒体运营助理","自动贩卖机寝室补货员（男生）","学校宣传部新闻宣传助理","自动贩卖机寝室补货员（女生）","城市大脑研究院助理","教育发展中心校园文明秩序引导员","cc梦工厂服务员","图书馆文化建设与阅读推厂助理"};
        String[] content={"包括接受线下订餐，打印线上外卖订单，通知后厨，打包餐盒等。上班需要健康证以及所需材料，要求年满十八岁，不包吃住，有优惠员工餐，入职为打卡制，根据学生空闲时间确定。","1.写通讯稿或编辑文案，负责档案资料的收集整理归档及电子档案的建立。2.负责办公室日常行政事务处理。3.办公室物资的领用管理。",
                "1.写通讯稿或编辑文案，负责档案资料的收集整理归档及电子档案的建立。2.负责办公室日常行政事务处理。3.组织实施各类学术活动。4.办公室物资的领用管理。","1.引导并讲解展览馆。2.及时清理浏览后留下的垃圾。3.关注用水用电安全",
                "1.负责每周作业的检查与批改，核对学生作业提交时间，及时记录迟交、未交、作业不全等情况。2.负责作业题目的通知。3.及时解决老师下发的任务。4.对每位同学的平时成绩进行统计。","1.引导学生正确进行体测项目并监督是否作弊。2.帮助摆放部分体侧设备",
                "1. 管理军训班级的日常事务。2. 负责学员的奖惩记录","制作出售客人订购的咖啡等产品","1.具有稿件采写，编辑，新媒体运营等综合能力。2.能协助完成图片视频后期处理等工作。3.VR学习室值班工作。4.协助完成每周快讯文档的整合并定期交稿",
                "宣传节约粮食反对浪费，引导文明用餐秩序。","熟练掌握办公软件应用技能,具备一定文字功底,有较好的表达能力,思维灵活。","负责制作出售客人在店内订购的咖啡","1.文字功底好,擅长写通讯稿或编辑文案；2.或者对自媒体感兴趣,对微信公众号或网站等媒体发布和编辑有一定的了解或经验；3.或者有一定的编辑或摄影爱好,擅长对校园活动或文化的拍摄。",
                "1.学习能力强,熟练掌握机器的操作方法和流程。2.勤快积极,能按时补货,及时清除机器故障","1.会摄影或者摄影技术为佳。2.会运用秀米，PS等软件。3.工作认真,勤恳踏实","1.学习能力强,熟练掌握机器的操作方法和流程。2.勤快积极,能按时补货,及时清除机器故障",
                "1.负责档案资料的收集整理归档及电子档案的建立；2.负责办公室日常行政事务处理；3.组织实施各类学术活动；4.办公室物资的领用管理。","耐心细致地指导老师和同学们进行“刷脸”、“刷卡”等操作，解答大家的各类疑问,快速处理支付过程中遇到的相关问题等。",
                "cc梦工厂前台，制作销售客人订购的产品","负责阅读推广的相关信息的搜集、编辑、策划等工作，有一定的文字功底，具有图片、多媒体制作等相关技能。"};
        String[] time={"周一~周五9:00-21:00","周一~周五8:00-17:00","周一~周五8:00-17:00","周一~周五8:00-17:00","每周一第3、4节课，周三第6、7节课","周一~周五8:00-17:00","2022.2.15-2022.3.2","周一~周五8:00-18:00",
                "周一~周五8:00-17:00","周一~周五7:00-9:00，11:00-13:00，16:00-18:00","周一~周五8:00-17:00","周一~周五8:00-17:00","周一~周五8:00-17:00","周一~周五18:00-22:00","周一~周五8:00-17:00","周一~周五8:00-17:00",
                "周一~周五8:00-17:00","周一~周五8:00-17:00","周一~周五8:00-17:00","周一~周五8:00-17:00"};
        String[] area={"浙大城市学院养生猪肚鸡档口","浙大城市学院图书馆","浙大城市学院行政楼","浙大城市学院行政楼","浙大城市学院理四218","浙大城市学院北校区操场","浙大城市学院","浙大城市学院北校区陈家河桥上","浙大城市学院图书馆",
                "浙大城市学院食堂","浙大城市学院行政楼","浙大城市学院文六楼","浙大城市学院妇联","浙大城市学院寝室楼","浙大城市学院行政楼","浙大城市学院寝室楼","浙大城市学院城市大脑研究院","浙大城市学院校内","浙大城市学院cc梦工厂（问源楼一楼）","浙大城市学院图书馆"};
        double[] salary={15,15,15,15,20,15,150,19,15,15,15,19,15,15,15,15,15,15,19,15};
        String[] payment={"月结","月结","月结","月结","月结","月结","月结","月结","月结","月结","月结","月结","月结","月结","月结","月结","月结","月结","月结","月结"};
        String[] publisher={"12345678910","16846556120","15466562321","18809216321","13315551333","16545313546","0571-88018801","0571-87677755","0571-87677753","0571-87677751","0571-87677750","0571-87677758","0571-87677759",
                "0571-87677725","0571-87687725","0571-87677225","0571-87617725","0571-87677620","0571-88077625","0571-88667603"};
        String[] publisherName={"陈阿姨","朱老师","宋老师","陈老师","李老师","马老师","计算学院学工办","李先生","张老师","林老师","龚老师","宋女士","朱老师","马先生","郑老师","马先生","陈老师","胡老师","李女士","郑老师"};
        int[] numofLikes={45,46,50,28,65,45,75,80,40,41,35,46,67,38,65,90,91,79,56,30};
        //插入20条记录
        for(int i=0;i<20;i++){
            ContentValues contentValues=new ContentValues();
            contentValues.put("WorkSerials",serials[i]);
            contentValues.put("WorkName",name[i]);
            contentValues.put("WorkContent",content[i]);
            contentValues.put("WorkTime",time[i]);
            contentValues.put("WorkArea",area[i]);
            contentValues.put("SalaryPerHour",salary[i]);
            contentValues.put("PaymentWay",payment[i]);
            contentValues.put("PublisherInfo",publisher[i]);
            contentValues.put("PublisherName",publisherName[i]);
            contentValues.put("NumOfLikes",numofLikes[i]);
            db.insert("tbl_work",null,contentValues);
        }
        Log.d("add work info","成功添加兼职信息");

        //添加几条自定义的我的报名数据
        Log.d("add signup info","开始添加自定义我的报名信息");
        //1.已录取
        ContentValues values=new ContentValues();
        values.put("UserID" , LoginActivity.getCurrentUser());
        values.put("WorkSerials" , serials[3]);
        values.put("WorkName" , name[3]);
        values.put("WorkContent" , content[3]);
        values.put("WorkTime" , time[3]);
        values.put("WorkArea" , area[3]);
        values.put("SalaryPerHour" , salary[3]);
        values.put("PaymentWay" , payment[3]);
        values.put("PublisherInfo" , publisher[3]);
        SimpleDateFormat sdf = new SimpleDateFormat();// 格式化时间
        sdf.applyPattern("yyyy-MM-dd HH:mm:ss a");// a为am/pm的标记
        Date date = new Date();
        String currenttime=sdf.format(date);
        values.put("SignupCreateTime" , currenttime);
        values.put("SignupStatus" , "已录取");
        values.put("IsLike","点赞");
        db.insert("tbl_signup",null,values);
        //2.已结束
        ContentValues values2=new ContentValues();
        values2.put("UserID" , LoginActivity.getCurrentUser());
        values2.put("WorkSerials" , serials[5]);
        values2.put("WorkName" , name[5]);
        values2.put("WorkContent" , content[5]);
        values2.put("WorkTime" , time[5]);
        values2.put("WorkArea" , area[5]);
        values2.put("SalaryPerHour" , salary[5]);
        values2.put("PaymentWay" , payment[5]);
        values2.put("PublisherInfo" , publisher[5]);
        SimpleDateFormat sdf2 = new SimpleDateFormat();// 格式化时间
        sdf2.applyPattern("yyyy-MM-dd HH:mm:ss a");// a为am/pm的标记
        Date date2 = new Date();
        String currenttime2=sdf.format(date2);
        values2.put("SignupCreateTime" , currenttime2);
        values2.put("SignupStatus" , "已结束");
        values2.put("IsLike","点赞");
        db.insert("tbl_signup",null,values2);
        Log.d("add signup info","成功添加自定义我的报名信息");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("drop table if exists tbl_work");
//        db.execSQL("DROP TABLE IF EXISTS tbl_user");
        onCreate(db);
    }

    public static void add(SQLiteDatabase db, String UserID, String Password, String Sno, String Name, String School, String Gender, String PhoneNub, String IntensionType1, String IntensionType2){
        db.execSQL("INSERT INTO tbl_user (UserID,Password,Sno,Name,School,Gender,PhoneNub,IntensionType1,IntensionType2,RegisterTime) VALUES(?,?,?,?,?,?,?,?,?,?)",new Object[]{UserID,Password,Sno,Name,School,Gender,PhoneNub,IntensionType1,IntensionType2,new SimpleDateFormat("yyyy年MM月dd日").format(Calendar.getInstance().getTime())});
        Log.e("注册成功", UserID );
    }
    public static void delete(SQLiteDatabase db,String UserID,String Password){
        db.execSQL("DELETE FROM tbl_user WHERE UserID = AND Password ="+UserID+Password);
        Log.e("删除成功", UserID );
    }
    public static void updata(SQLiteDatabase db,String UserID,String PhoneNub,String IntensionType1,String IntensionType2){
        db.execSQL("UPDATE tbl_user  SET PhoneNub=?,IntensionType1=?,IntensionType2=? where UserID=? " ,new Object[]{ PhoneNub ,IntensionType1,IntensionType2,UserID});
        Log.e("修改成功", UserID );
    }

    public static ArrayList<User> getAllData(SQLiteDatabase db){
        ArrayList<User> list = new ArrayList<User>();
        @SuppressLint("Recycle") Cursor cursor = db.query("tbl_user",null,null,null,null,null,"UserID DESC");
        while(cursor.moveToNext()){
            @SuppressLint("Range") String UserID = cursor.getString(cursor.getColumnIndex("UserID"));
            @SuppressLint("Range")String Password = cursor.getString(cursor.getColumnIndex("Password"));
            @SuppressLint("Range")String Sno = cursor.getString(cursor.getColumnIndex("Sno"));
            @SuppressLint("Range")String Name = cursor.getString(cursor.getColumnIndex("Name"));
            @SuppressLint("Range")String School = cursor.getString(cursor.getColumnIndex("School"));
            @SuppressLint("Range")String Gender = cursor.getString(cursor.getColumnIndex("Gender"));
            @SuppressLint("Range")String PhoneNub = cursor.getString(cursor.getColumnIndex("PhoneNub"));
            @SuppressLint("Range")String IntensionType1 = cursor.getString(cursor.getColumnIndex("IntensionType1"));
            @SuppressLint("Range")String IntensionType2 = cursor.getString(cursor.getColumnIndex("IntensionType2"));

            list.add(new User(UserID,Password,Sno,Name,Gender,School,PhoneNub,IntensionType1,IntensionType2));
        }
        return list;
    }


}